<html>
<head>
  <title>16-optimizer trace 表的神奇功效</title>
  <basefont face="微软雅黑" size="2" />
  <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
  <meta name="exporter-version" content="YXBJ Windows/600646 (zh-CN, DDL); Windows/6.1.1 (Win64);"/>
  <meta name="content-class" content="yinxiang.markdown"/>
  <style>
    body, td {
      font-family: 微软雅黑;
      font-size: 10pt;
    }
  </style>
</head>
<body>
<a name="887"/>
<h1>16-optimizer trace 表的神奇功效</h1>

<div><span><div style="font-size: 14px; margin: 0; padding: 0; width: 100%;"><p style="line-height: 160%; box-sizing: content-box; margin: 10px 0; color: #333;">MySQL 5.6 以及之后版本中，MySQL提出了一个<strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">optimizer trace</strong>的功能，这个功能可以很方便的查看优化器生成执行计划的整个过程，这个功能的开启与关闭由系统变量<strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">optimizer_trace</strong>控制。</p>
<p style="line-height: 160%; box-sizing: content-box; margin: 10px 0; color: #333;"><img src="16-optimizer trace 表的神奇功效_files/Image.png" type="image/png" data-filename="Image.png"/></p>
<p style="line-height: 160%; box-sizing: content-box; margin: 10px 0; color: #333;">可以看到<strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">enabled值为off</strong>，即这个功能默认是关闭的。</p>
<p style="line-height: 160%; box-sizing: content-box; margin: 10px 0; color: #333;">one_line的值是控制输出格式的，如果为on代表所有的输出内容将在一行中展示，一般都设置为off。</p>
<p style="line-height: 160%; box-sizing: content-box; margin: 10px 0; color: #333;">如果想打开这个功能，必须首先把enabled的值设置为on：<br/>
<img src="16-optimizer trace 表的神奇功效_files/Image [1].png" type="image/png" data-filename="Image.png"/></p>
<p style="line-height: 160%; box-sizing: content-box; margin: 10px 0; color: #333;">然后可以输入想要查看优化过程的语句，当该查询语句执行完成后，就可以到<strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">information_schema</strong>数据库下的<strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">optimizer_trace表</strong>中查看完整的优化过程，这个optimizer_trace表有4个列，分别是：</p>
<ul style="line-height: 160%; box-sizing: content-box; display: block; list-style-type: disc; padding-left: 30px; margin: 6px 0 10px; color: #333;">
<li style="line-height: 160%; box-sizing: content-box; position: relative;"><strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">query</strong>：表示查询的语句</li>
<li style="line-height: 160%; box-sizing: content-box; position: relative;"><strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">trace</strong>：表示优化过程的JSON格式文本</li>
<li style="line-height: 160%; box-sizing: content-box; position: relative;"><strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">missing_bytes_beyond_max_men_size</strong>：由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数</li>
<li style="line-height: 160%; box-sizing: content-box; position: relative;"><strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">insufficient_privileges</strong>：表示是否没有权限查看优化过程，默认值是0，只有某些特殊情况下才会是1。</li>
</ul>
<p style="line-height: 160%; box-sizing: content-box; margin: 10px 0; color: #333;"><strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">完整的使用optimizer trace功能的步骤总结如下：</strong></p>
<p style="line-height: 160%; box-sizing: content-box; margin: 10px 0; color: #333;"><img src="16-optimizer trace 表的神奇功效_files/Image [2].png" type="image/png" data-filename="Image.png"/></p>
<p style="line-height: 160%; box-sizing: content-box; margin: 10px 0; color: #333;">例如查询：</p>
<p style="line-height: 160%; box-sizing: content-box; margin: 10px 0; color: #333;">SELECT * FROM s1 WHERE<br/>
key1 &gt; 'z' AND<br/>
key2 &lt; 1000000 AND<br/>
key3 IN ('a', 'b', 'c') AND<br/>
common_field = 'abc'；</p>
<p style="line-height: 160%; box-sizing: content-box; margin: 10px 0; color: #333;">通过查询information_schema.optimizer_trace表的trace（优化过程）大致分为3个阶段：</p>
<ul style="line-height: 160%; box-sizing: content-box; display: block; list-style-type: disc; padding-left: 30px; margin: 6px 0 10px; color: #333;">
<li style="line-height: 160%; box-sizing: content-box; position: relative;"><strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">prepare阶段</strong></li>
<li style="line-height: 160%; box-sizing: content-box; position: relative;"><strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">optimize阶段</strong><br/>
optimizer阶段主要分为几个：<br/>
a. 处理搜索条件：等值传递转换、常量传递转换、去除没用的条件；<br/>
b. 预估不同单表访问方法的访问成本（<strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">rows_estimation</strong>）：如果是单表查询主要关注这个部分；<br/>
c. 分析各种可能的执行计划（<strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">considered_execution_plans</strong>）：连接查询主要关注这个部分；</li>
<li style="line-height: 160%; box-sizing: content-box; position: relative;"><strong style="line-height: 160%; box-sizing: content-box; font-weight: 700;">execute阶段</strong></li>
</ul>
</div><center style="display:none !important;visibility:collapse !important;height:0 !important;white-space:nowrap;width:100%;overflow:hidden">MySQL%205.6%20%E4%BB%A5%E5%8F%8A%E4%B9%8B%E5%90%8E%E7%89%88%E6%9C%AC%E4%B8%AD%EF%BC%8CMySQL%E6%8F%90%E5%87%BA%E4%BA%86%E4%B8%80%E4%B8%AA**optimizer%20trace**%E7%9A%84%E5%8A%9F%E8%83%BD%EF%BC%8C%E8%BF%99%E4%B8%AA%E5%8A%9F%E8%83%BD%E5%8F%AF%E4%BB%A5%E5%BE%88%E6%96%B9%E4%BE%BF%E7%9A%84%E6%9F%A5%E7%9C%8B%E4%BC%98%E5%8C%96%E5%99%A8%E7%94%9F%E6%88%90%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92%E7%9A%84%E6%95%B4%E4%B8%AA%E8%BF%87%E7%A8%8B%EF%BC%8C%E8%BF%99%E4%B8%AA%E5%8A%9F%E8%83%BD%E7%9A%84%E5%BC%80%E5%90%AF%E4%B8%8E%E5%85%B3%E9%97%AD%E7%94%B1%E7%B3%BB%E7%BB%9F%E5%8F%98%E9%87%8F**optimizer_trace**%E6%8E%A7%E5%88%B6%E3%80%82%0A%0A!%5Bdda631516a8afdb7e685db2e265c7d71.png%5D(en-resource%3A%2F%2Fdatabase%2F889%3A1)%0A%0A%E5%8F%AF%E4%BB%A5%E7%9C%8B%E5%88%B0**enabled%E5%80%BC%E4%B8%BAoff**%EF%BC%8C%E5%8D%B3%E8%BF%99%E4%B8%AA%E5%8A%9F%E8%83%BD%E9%BB%98%E8%AE%A4%E6%98%AF%E5%85%B3%E9%97%AD%E7%9A%84%E3%80%82%0A%0Aone_line%E7%9A%84%E5%80%BC%E6%98%AF%E6%8E%A7%E5%88%B6%E8%BE%93%E5%87%BA%E6%A0%BC%E5%BC%8F%E7%9A%84%EF%BC%8C%E5%A6%82%E6%9E%9C%E4%B8%BAon%E4%BB%A3%E8%A1%A8%E6%89%80%E6%9C%89%E7%9A%84%E8%BE%93%E5%87%BA%E5%86%85%E5%AE%B9%E5%B0%86%E5%9C%A8%E4%B8%80%E8%A1%8C%E4%B8%AD%E5%B1%95%E7%A4%BA%EF%BC%8C%E4%B8%80%E8%88%AC%E9%83%BD%E8%AE%BE%E7%BD%AE%E4%B8%BAoff%E3%80%82%0A%0A%E5%A6%82%E6%9E%9C%E6%83%B3%E6%89%93%E5%BC%80%E8%BF%99%E4%B8%AA%E5%8A%9F%E8%83%BD%EF%BC%8C%E5%BF%85%E9%A1%BB%E9%A6%96%E5%85%88%E6%8A%8Aenabled%E7%9A%84%E5%80%BC%E8%AE%BE%E7%BD%AE%E4%B8%BAon%EF%BC%9A%0A!%5Bd87bf9c2ad2338c01309911bf33b5a68.png%5D(en-resource%3A%2F%2Fdatabase%2F891%3A1)%0A%0A%E7%84%B6%E5%90%8E%E5%8F%AF%E4%BB%A5%E8%BE%93%E5%85%A5%E6%83%B3%E8%A6%81%E6%9F%A5%E7%9C%8B%E4%BC%98%E5%8C%96%E8%BF%87%E7%A8%8B%E7%9A%84%E8%AF%AD%E5%8F%A5%EF%BC%8C%E5%BD%93%E8%AF%A5%E6%9F%A5%E8%AF%A2%E8%AF%AD%E5%8F%A5%E6%89%A7%E8%A1%8C%E5%AE%8C%E6%88%90%E5%90%8E%EF%BC%8C%E5%B0%B1%E5%8F%AF%E4%BB%A5%E5%88%B0**information_schema**%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%8B%E7%9A%84**optimizer_trace%E8%A1%A8**%E4%B8%AD%E6%9F%A5%E7%9C%8B%E5%AE%8C%E6%95%B4%E7%9A%84%E4%BC%98%E5%8C%96%E8%BF%87%E7%A8%8B%EF%BC%8C%E8%BF%99%E4%B8%AAoptimizer_trace%E8%A1%A8%E6%9C%894%E4%B8%AA%E5%88%97%EF%BC%8C%E5%88%86%E5%88%AB%E6%98%AF%EF%BC%9A%0A%0A*%20**query**%EF%BC%9A%E8%A1%A8%E7%A4%BA%E6%9F%A5%E8%AF%A2%E7%9A%84%E8%AF%AD%E5%8F%A5%0A*%20**trace**%EF%BC%9A%E8%A1%A8%E7%A4%BA%E4%BC%98%E5%8C%96%E8%BF%87%E7%A8%8B%E7%9A%84JSON%E6%A0%BC%E5%BC%8F%E6%96%87%E6%9C%AC%0A*%20**missing_bytes_beyond_max_men_size**%EF%BC%9A%E7%94%B1%E4%BA%8E%E4%BC%98%E5%8C%96%E8%BF%87%E7%A8%8B%E5%8F%AF%E8%83%BD%E4%BC%9A%E8%BE%93%E5%87%BA%E5%BE%88%E5%A4%9A%EF%BC%8C%E5%A6%82%E6%9E%9C%E8%B6%85%E8%BF%87%E6%9F%90%E4%B8%AA%E9%99%90%E5%88%B6%E6%97%B6%EF%BC%8C%E5%A4%9A%E4%BD%99%E7%9A%84%E6%96%87%E6%9C%AC%E5%B0%86%E4%B8%8D%E4%BC%9A%E8%A2%AB%E6%98%BE%E7%A4%BA%EF%BC%8C%E8%BF%99%E4%B8%AA%E5%AD%97%E6%AE%B5%E5%B1%95%E7%A4%BA%E4%BA%86%E8%A2%AB%E5%BF%BD%E7%95%A5%E7%9A%84%E6%96%87%E6%9C%AC%E5%AD%97%E8%8A%82%E6%95%B0%0A*%20**insufficient_privileges**%EF%BC%9A%E8%A1%A8%E7%A4%BA%E6%98%AF%E5%90%A6%E6%B2%A1%E6%9C%89%E6%9D%83%E9%99%90%E6%9F%A5%E7%9C%8B%E4%BC%98%E5%8C%96%E8%BF%87%E7%A8%8B%EF%BC%8C%E9%BB%98%E8%AE%A4%E5%80%BC%E6%98%AF0%EF%BC%8C%E5%8F%AA%E6%9C%89%E6%9F%90%E4%BA%9B%E7%89%B9%E6%AE%8A%E6%83%85%E5%86%B5%E4%B8%8B%E6%89%8D%E4%BC%9A%E6%98%AF1%E3%80%82%0A%0A%0A**%E5%AE%8C%E6%95%B4%E7%9A%84%E4%BD%BF%E7%94%A8optimizer%20trace%E5%8A%9F%E8%83%BD%E7%9A%84%E6%AD%A5%E9%AA%A4%E6%80%BB%E7%BB%93%E5%A6%82%E4%B8%8B%EF%BC%9A**%0A%0A!%5Bde0db90661688a65e18146cebf095f3f.png%5D(en-resource%3A%2F%2Fdatabase%2F893%3A1)%0A%0A%0A%E4%BE%8B%E5%A6%82%E6%9F%A5%E8%AF%A2%EF%BC%9A%0A%0ASELECT%20*%20FROM%20s1%20WHERE%20%0Akey1%20%3E%20'z'%20AND%20%0Akey2%20%3C%201000000%20AND%20%0Akey3%20IN%20('a'%2C%20'b'%2C%20'c')%20AND%20%0Acommon_field%20%3D%20'abc'%EF%BC%9B%0A%0A%0A%E9%80%9A%E8%BF%87%E6%9F%A5%E8%AF%A2information_schema.optimizer_trace%E8%A1%A8%E7%9A%84trace%EF%BC%88%E4%BC%98%E5%8C%96%E8%BF%87%E7%A8%8B%EF%BC%89%E5%A4%A7%E8%87%B4%E5%88%86%E4%B8%BA3%E4%B8%AA%E9%98%B6%E6%AE%B5%EF%BC%9A%0A%0A*%20**prepare%E9%98%B6%E6%AE%B5**%0A*%20**optimize%E9%98%B6%E6%AE%B5**%0Aoptimizer%E9%98%B6%E6%AE%B5%E4%B8%BB%E8%A6%81%E5%88%86%E4%B8%BA%E5%87%A0%E4%B8%AA%EF%BC%9A%0Aa.%20%E5%A4%84%E7%90%86%E6%90%9C%E7%B4%A2%E6%9D%A1%E4%BB%B6%EF%BC%9A%E7%AD%89%E5%80%BC%E4%BC%A0%E9%80%92%E8%BD%AC%E6%8D%A2%E3%80%81%E5%B8%B8%E9%87%8F%E4%BC%A0%E9%80%92%E8%BD%AC%E6%8D%A2%E3%80%81%E5%8E%BB%E9%99%A4%E6%B2%A1%E7%94%A8%E7%9A%84%E6%9D%A1%E4%BB%B6%EF%BC%9B%0Ab.%20%E9%A2%84%E4%BC%B0%E4%B8%8D%E5%90%8C%E5%8D%95%E8%A1%A8%E8%AE%BF%E9%97%AE%E6%96%B9%E6%B3%95%E7%9A%84%E8%AE%BF%E9%97%AE%E6%88%90%E6%9C%AC%EF%BC%88**rows_estimation**%EF%BC%89%EF%BC%9A%E5%A6%82%E6%9E%9C%E6%98%AF%E5%8D%95%E8%A1%A8%E6%9F%A5%E8%AF%A2%E4%B8%BB%E8%A6%81%E5%85%B3%E6%B3%A8%E8%BF%99%E4%B8%AA%E9%83%A8%E5%88%86%EF%BC%9B%0Ac.%20%E5%88%86%E6%9E%90%E5%90%84%E7%A7%8D%E5%8F%AF%E8%83%BD%E7%9A%84%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92%EF%BC%88**considered_execution_plans**%EF%BC%89%EF%BC%9A%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2%E4%B8%BB%E8%A6%81%E5%85%B3%E6%B3%A8%E8%BF%99%E4%B8%AA%E9%83%A8%E5%88%86%EF%BC%9B%0A*%20**execute%E9%98%B6%E6%AE%B5**</center></span>
</div></body></html> 